package com.pb.metadata.service;

import com.pb.metadata.mapper.DataMigrationMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.*;

/**
 * 元数据迁移
 * @author liaohaoyuan
 * @date 20210301
 */
@Service
public class DataMigrationService {

    /**
     * DAO映射对象
     */
    @Resource
    private DataMigrationMapper dataMigrationMapper;


    /**
     * 导出第一个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet1(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet1(map);
    }

    /**
     * 导出第二个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet2(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet2(map);
    }

    /**
     * 导出第三个sheet页信息
     * @param map 请求参数
     * @return
     */
    /*public  List<Map<String,Object>> selectDataListSheet3(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet3(map);
    }*/

    /**
     * 导出第四个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet4(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet4(map);
    }

    /**
     * 导出第五个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet5(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet5(map);
    }

    /**
     * 导出第六个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet6(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet6(map);
    }

    /**
     * 导出第七个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet7(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet7(map);
    }

    /**
     * 导出第八个sheet页信息
     * @param map 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet8(Map<String, Object> map){
        return  dataMigrationMapper.selectDataListSheet8(map);
    }

    /**
     * 导出第九个sheet页信息
     * @param list 请求参数
     * @return
     */
    public  List<Map<String,Object>> selectDataListSheet9(List<Map<String,Object>> list){
        return  dataMigrationMapper.selectDataListSheet9(list);
    }
    /**
     * 导入
     * @param data
     */
    @Transactional(readOnly = true)
    public String importExcel(List<List<Map<String,Object>>> data){
        String msg="";
        StringBuffer stringBuffer = new StringBuffer();
        StringBuffer sbf=new StringBuffer();
        if(data.size()!=9){
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/><br>请选择正确的数据迁移文件";
            return msg;
        }

        List<Map<String, Object>> sheetList1 =data.get(0);
        List keyList1=dataMigrationMapper.getKeyList1();
        String keyValue1="databaseId";
        Map<String,List> List1Map=this.getListMap(keyList1,sheetList1,keyValue1);
        List<Map<String, Object>>  sheetAddList1 =List1Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList1=List1Map.get("sheetUpdateList");
        try {
            if(sheetAddList1.size()>0){
                List subAddList=this.getSubList(sheetAddList1);
                dataMigrationMapper.addSheetData1(subAddList);
            }
            if(sheetUpdateList1.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList1);
                dataMigrationMapper.updateSheetData1(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_database相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList2 =data.get(1);
        List keyList2=dataMigrationMapper.getKeyList2();
        String keyValue2="databaseUserId";
        Map<String,List> List2Map=this.getListMap(keyList2,sheetList2,keyValue2);
        List<Map<String, Object>>  sheetAddList2 =List2Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList2=List2Map.get("sheetUpdateList");
        try {
            if(sheetAddList2.size()>0){
                List subAddList=this.getSubList(sheetAddList2);
                dataMigrationMapper.addSheetData2(subAddList);
            }
            if(sheetUpdateList2.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList2);
                dataMigrationMapper.updateSheetData2(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_database_user相关错误数据";
            return msg;
        }


        /*List<Map<String,Object>>  sheetList3 =data.get(2);
        List keyList3=dataMigrationMapper.getKeyList3();
        String keyValue3="appId";
        Map<String,List> List3Map=this.getListMap(keyList3,sheetList3,keyValue3);
        List<Map<String, Object>>  sheetAddList3 =List3Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList3=List3Map.get("sheetUpdateList");
        try {
            if(sheetAddList3.size()>0){
                List subAddList=this.getSubList(sheetAddList3);
                dataMigrationMapper.addSheetData3(subAddList);
            }
            if(sheetUpdateList3.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList3);
                dataMigrationMapper.updateSheetData3(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_app相关错误数据";
            return msg;
        }*/


        List<Map<String,Object>>  sheetList4 =data.get(2);
        List keyList4=dataMigrationMapper.getKeyList4();
        String keyValue4="columnId";
        Map<String,List> List4Map=this.getListMap(keyList4,sheetList4,keyValue4);
        List<Map<String, Object>>  sheetAddList4 =List4Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList4=List4Map.get("sheetUpdateList");
        try {
            if(sheetAddList4.size()>0){
                List subAddList=this.getSubList(sheetAddList4);
                dataMigrationMapper.addSheetData4(subAddList);
            }
            if(sheetUpdateList4.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList4);
                dataMigrationMapper.updateSheetData4(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_column相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList5 =data.get(3);
        List keyList5=dataMigrationMapper.getKeyList5();
        String keyValue5="datasourceId";
        Map<String,List> List5Map=this.getListMap(keyList5,sheetList5,keyValue5);
        List<Map<String, Object>>  sheetAddList5 =List5Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList5=List5Map.get("sheetUpdateList");
        try {
            if(sheetAddList5.size()>0){
                List subAddList=this.getSubList(sheetAddList5);
                dataMigrationMapper.addSheetData5(subAddList);
            }
            if(sheetUpdateList5.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList5);
                dataMigrationMapper.updateSheetData5(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_datasource相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList6 =data.get(4);
        List keyList6=dataMigrationMapper.getKeyList6();
        String keyValue6="datasourceColumnId";
        Map<String,List> List6Map=this.getListMap(keyList6,sheetList6,keyValue6);
        List<Map<String, Object>>  sheetAddList6 =List6Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList6=List6Map.get("sheetUpdateList");
        try {
            if(sheetAddList6.size()>0){
                List subAddList=this.getSubList(sheetAddList6);
                dataMigrationMapper.addSheetData6(subAddList);
            }
            if(sheetUpdateList6.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList6);
                dataMigrationMapper.updateSheetData6(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_datasource_column相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList7 =data.get(5);
        List keyList7=dataMigrationMapper.getKeyList7();
        String keyValue7="codeTableId";
        Map<String,List> List7Map=this.getListMap(keyList7,sheetList7,keyValue7);
        List<Map<String, Object>>  sheetAddList7 =List7Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList7=List7Map.get("sheetUpdateList");
        try {
            if(sheetAddList7.size()>0){
                List subAddList=this.getSubList(sheetAddList7);
                dataMigrationMapper.addSheetData7(subAddList);
            }
            if(sheetUpdateList7.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList7);
                dataMigrationMapper.updateSheetData7(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_code_table相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList8 =data.get(6);
        List keyList8=dataMigrationMapper.getKeyList8();
        String keyValue8="codeTypeId";
        Map<String,List> List8Map=this.getListMap(keyList8,sheetList8,keyValue8);
        List<Map<String, Object>>  sheetAddList8 =List8Map.get("sheetAddList");
        List<Map<String, Object>>  sheetUpdateList8=List8Map.get("sheetUpdateList");
        try {
            if(sheetAddList8.size()>0){
                List subAddList=this.getSubList(sheetAddList8);
                dataMigrationMapper.addSheetData8(subAddList);
            }
            if(sheetUpdateList8.size()>0){
                List subUpdateList=this.getSubList(sheetUpdateList8);
                dataMigrationMapper.updateSheetData8(subUpdateList);
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改表param_code_type相关错误数据";
            return msg;
        }

        List<Map<String,Object>>  sheetList9 =data.get(7);
        HashSet<String> paraSet = new HashSet<String>();
        for (Map<String, Object> map : sheetList9) {
            String tableName=(String)map.get("tableName")+"_bak";
            boolean exitFlag=this.exitTableByName(tableName);
            if(exitFlag){
                dataMigrationMapper.deleteCodeTableDate(tableName);
            }else{
                paraSet.add(tableName);
            }
        }

        for(String tableName:paraSet){
            dataMigrationMapper.createNewTable(tableName);
        }

        try {
            if (sheetList9.size() > 0) {
                for(Map map:sheetList9){
                    String tableName=(String)map.get("tableName");
                    map.put("tableName",tableName+"_bak");
                    dataMigrationMapper.addSheetData9(map);
                }
            }
        } catch (Exception e){
            e.printStackTrace();
            msg="<span style='color:red;font-weight:bold;'>======>导入失败</span><span style='color:red;font-weight:bold;'><=====</span> <br/>"+ e.getCause().getMessage()+"<br>请修改码值信息相关错误数据";
            return msg;
        }

        msg= "**********   <span style='color:green;font-weight:bold;'>导入结果：成功</span>   " +
                "********** <br/><br/>其中，param_database 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList1.size() + "</span> 条数据；"+
                "<br/><br/> param_database_user 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList2.size() + "</span> 条数据；"+
                /*"<br/><br/> param_app 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList3.size() + "</span> 条数据；"+*/
                "<br/><br/> param_column 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList4.size() + "</span> 条数据；"+
                "<br/><br/> param_datasource 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList5.size() + "</span> 条数据；"+
                "<br/><br/> param_datasource_column 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList6.size() + "</span> 条数据；"+
                "<br/><br/> param_code_table 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList7.size() + "</span> 条数据；"+
                "<br/><br/> param_code_type 表共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList8.size() + "</span> 条数据。"+
                "<br/><br/> 码值信息共计导入 <span style='color:blue;font-weight:bold;'>"+ sheetList9.size() + "</span> 条数据。";
        return msg;
    }

    /**
     * 判断表是否存在
     * @param tableName 键
     * @return true : 存在 ；false：不存在
     */
    public boolean exitTableByName(String tableName){
        boolean flag=false;
        try {
            Map<String, Object> map=new HashMap<String, Object>();
            map.put("tableName",tableName);
            String str= dataMigrationMapper.exitTableByName(map);
            flag=true;
        }catch (Exception e){
            e.printStackTrace();
            flag=false;
        }
        return flag;
    }

    /**
     * 查询所有是否存目标字符
     * @param valueList 目标List
     * @param str 码值标识
     * @param key 键值
     * @return
     */
    private boolean containsByKey(List<Map<String, Object>> valueList, String str, String key){
        for(int i = 0; i < valueList.size(); i++){
            if(valueList.get(i).get(key).equals(str)){
                return true;
            }
        }
        return false;
    }

    public Map<String,List> getListMap(List<Map<String, Object>> keyList,List<Map<String,Object>> sheetList,String keyVal){
        Map<String,List> dataMap = new HashMap<String,List>();
        List<Map<String, Object>> sheetAddList =new ArrayList<>();
        List<Map<String, Object>> sheetUpdateList=new ArrayList<>();
        boolean keyFlag=false;
        for (Map<String, Object> map : sheetList) {
            String key=(String)map.get(keyVal);
            keyFlag=this.containsByKey(keyList,key,"keyValue");
            if(keyFlag){
                sheetUpdateList.add(map);
            }else{
                sheetAddList.add(map);
            }
        }
        dataMap.put("sheetAddList",sheetAddList);
        dataMap.put("sheetUpdateList",sheetUpdateList);
        return dataMap;
    }

    public  List<Map<String,Object>> getSubList(List<Map<String,Object>> list){
        List<Map<String,Object>> subList=new ArrayList<Map<String,Object>>();
        if(list.size()>1600){
            int length = list.size();
            int groupSize=1600;
            // 计算可以分成多少组
            int num = (length + groupSize - 1) / groupSize;
            for (int i = 0; i < num; i++) {
                // 开始位置
                int fromIndex = i * groupSize;
                // 结束位置
                int toIndex = (i + 1) * groupSize < length ? (i + 1) * groupSize : length;
                subList=list.subList(fromIndex, toIndex);
            }
        }else{
            subList=list;
        }
        return subList;

    }
}
